package com.lagou.task.dao;

import com.lagou.task.model.Student;
import com.lagou.task.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class StudentDaoImp implements StudentDao{
    @Override
    public int add(Student std) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtil.getConnection();
            String sql = "insert t_student values(null,?,?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,std.getsName());
            preparedStatement.setString(2,std.getsSex());
            preparedStatement.setString(3, std.getsBirth());
            preparedStatement.setString(4,std.getsEmail());
            preparedStatement.setString(5, std.getsNote());
            int row = preparedStatement.executeUpdate();
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DBUtil.closeSource(connection,preparedStatement);
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
        return 0;
    }

    @Override
    public List<Student> selectAll() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Student> list= new ArrayList<>();
        try {
            connection = DBUtil.getConnection();
            String sql = "select * from t_student";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while ((resultSet.next())){
                Student student = new Student();
                student.setsId((resultSet.getInt("sId")));
                student.setsName(resultSet.getString("sName"));
                student.setsSex(resultSet.getString("sSex"));
                student.setsBirth(resultSet.getString("sBirth"));
                student.setsEmail(resultSet.getString("sEmail"));
                student.setsNote(resultSet.getString("sNote"));
                list.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                DBUtil.closeSource(connection,preparedStatement);
                if(null !=resultSet){
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    @Override
    public int update(String sId,String sName, String sSex, String sBirth, String sEmail, String sNote) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtil.getConnection();
            String sql = "update t_student set sName = ?,sSex = ?,sBirth = ?,sEmail = ?,sNote = ? where sId = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,sName);
            preparedStatement.setString(2,sSex);
            preparedStatement.setString(3, sBirth);
            preparedStatement.setString(4,sEmail);
            preparedStatement.setString(5,sNote);
            preparedStatement.setString(6, sId);
            int row = preparedStatement.executeUpdate();
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DBUtil.closeSource(connection,preparedStatement);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    @Override
    public int delete(int sId) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtil.getConnection();
            String sql = "delete from t_student where sId = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,sId);
            int row = preparedStatement.executeUpdate();
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                DBUtil.closeSource(connection,preparedStatement);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    @Override
    public Student selectById(String string) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Student student = new Student();
        try {
            connection = DBUtil.getConnection();
            String sql = "select * from t_student where sId = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,string);
            resultSet = preparedStatement.executeQuery();
            while ((resultSet.next())){
                student.setsId((resultSet.getInt("sId")));
                student.setsName(resultSet.getString("sName"));
                student.setsSex(resultSet.getString("sSex"));
                student.setsBirth(resultSet.getString("sBirth"));
                student.setsEmail(resultSet.getString("sEmail"));
                student.setsNote(resultSet.getString("sNote"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                DBUtil.closeSource(connection,preparedStatement);
                if(null !=resultSet){
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return student;
    }
}
